﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Deployment.Application;

namespace NhapXuatTon
{
    class Clsdatabase
    {
        private const string Strconnect = "Data Source=localhost;Initial Catalog=NhapXuatTon;Integrated Security=True";
        SqlConnection _sqlcon;
        SqlCommand _sqlcom;
        SqlDataAdapter _sqlda;
        SqlDataReader _sqldr;
        DataSet ds = new DataSet();
        

        void Ketnoi()
        {
            _sqlcon = new SqlConnection(Strconnect);
            _sqlcon.Open(); 
        }

        void Ngatketnoi()
        {
            _sqlcon.Close();
        } 

        public void Thucthiketnoi(string strsql)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strsql, _sqlcon);
            _sqlcom.ExecuteNonQuery();
            Ngatketnoi();
        }

        public void Loaddatagridview(DataGridView dg, string strselect)
        {
            ds.Clear();
            _sqlda = new SqlDataAdapter(strselect, Strconnect);
            _sqlda.Fill(ds, "tenbang");
            dg.DataSource = ds.Tables[0];
        }

        public void Loaddatagridview1(DataGridView dg,DataSet ds1, string strselect)
        {
            ds1.Clear();
            _sqlda = new SqlDataAdapter(strselect, Strconnect);
            _sqlda.Fill(ds1, "tenbang");
            dg.DataSource = ds1.Tables[0];
        }        

        public void Loadcombobox(ComboBox cb, string strselect, byte chiso)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strselect, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                cb.Items.Add(_sqldr[chiso].ToString());
            }
            Ngatketnoi();
        }

        public void Loadtextbox(TextBox cb, string strselect)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strselect, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                cb.Text = _sqldr[0].ToString();
            }
            Ngatketnoi();       

        }

        public void Loaddatetime(DateTimePicker cb, string strselect,byte chiso)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strselect, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                cb.Text = _sqldr[chiso].ToString();
            }
            Ngatketnoi();
        }

        public void Loadtextboxchiso(TextBox cb, string strselect,byte chiso)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strselect, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                cb.Text = _sqldr[chiso].ToString();
            }
            Ngatketnoi();
        }

        public string Getdata(string dauvao, string strsql)
        {
            string strketqua = "";
            Ketnoi();
            _sqlcom = new SqlCommand(strsql, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                if (_sqldr[0].ToString().ToLower() == dauvao.ToLower())
                    strketqua = _sqldr[1].ToString();
            }
            Ngatketnoi();
            return strketqua;
        }

        public bool Kt(string dauvao, string strsql, byte chiso)
        {
            bool ok = false;
            Ketnoi();
            _sqlcom = new SqlCommand(strsql, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                if (_sqldr[chiso].ToString().ToLower() == dauvao.ToLower())
                    ok = true;
            }
            Ngatketnoi();
            return ok;
        }

        public string Thu(string dauvao, string strsql)
        {
            string strketqua = "";
            Ketnoi();
            _sqlcom = new SqlCommand(strsql, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                if (_sqldr[0].ToString().ToLower() == dauvao.ToLower())
                    strketqua = _sqldr[0].ToString();
            }
            Ngatketnoi();
            return strketqua;
        }

        public bool Kttrungkhoa(string dauvao, string strsql)
        {
            bool ok = false;
            Ketnoi();
            _sqlcom = new SqlCommand(strsql, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                if (_sqldr[0].ToString().ToLower() == dauvao.ToLower())
                    ok = true;
            }
            Ngatketnoi();
            return ok;
        }

        public void Loatextbox(TextBox tb, string strselect, byte chiso)
        {
            Ketnoi();
            _sqlcom = new SqlCommand(strselect, _sqlcon);
            _sqldr = _sqlcom.ExecuteReader();
            while (_sqldr.Read())
            {
                tb.Lines[0] = (_sqldr[chiso].ToString());
            }
            Ngatketnoi();
        }

    }
}
